/*
 * 文件名：ExcelUtils
 * 版权：Copyright @ 2017 mop All Rights Reserved.
 * 描述：
 * 修改人：lottery
 * 修改时间：2017年9月24日 下午12:33:34
 * 修改内容：〈修改内容〉
 */
package com.lemon.core.common.lang.util;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 * @desc: mop	只支持xls后缀名的excel文件
 * @author: lottery
 * @createTime: 2017年9月24日 下午12:33:34
 * @history:
 * @version: v1.0
 */
public class ExcelUtils {
	
	/**
	 * mop 文件扩展名
	 */
	private final static String EXCEL_FILE = ".xls";
	/**
	 * 文件编码格式-UTF-8
	 */
	private final static String UTF_FILE_CODE = "UTF-8";
	/**
	 * 响应头-名字
	 */
	private final static String RESPONSE_HEADER_NAME = "Content-Disposition";
	/**
	 * 响应头-值
	 */
	private final static String RESPONSE_HEADER_VALUE = "attachment;filename=";
	/**
	 * 响应文本类型
	 */
	private final static String RESPONSE_CONTENT_TYPE = "application/octet-stream;charset=";

	/**
	 * 首行单元格样式
	 */
	private static HSSFCellStyle headCellStyle = null;

	/**
	 * 数据单元格样式
	 */
	private static HSSFCellStyle dataCellStyle = null;

	// ----------------------------------- 解析excel -----------------------------------

	@SuppressWarnings("resource")
	public static <T> List<T> parseList(File file, Class<T> clazz) {
		List<T> list = new ArrayList<T>();
		try {
			List<String> properties = ReflectionUtils.getDeclaredFieldNames(clazz);

			// 获取workbook
			HSSFWorkbook workbook = new HSSFWorkbook(FileUtils.openInputStream(file));
			// 获取当前第一个sheet页
			HSSFSheet sheet = workbook.getSheetAt(0);
			// 获取行数
			int rowSize = sheet.getPhysicalNumberOfRows();
			// 直接从第三行开始获取数据
			if (rowSize > 1) {
				for (int i = 1; i < rowSize; i++) {
					T t = clazz.newInstance();
					Row row = sheet.getRow(i);
					// 获取单元格数
					int cellSize = row.getPhysicalNumberOfCells();
					for (int j = 0; j < cellSize; j++) {
						Object cellValue = getCellValue(row.getCell(j));
						if (cellValue != null) {
							ReflectionUtils.setFieldValue(t, properties.get(j), cellValue);
						}
					}
					list.add(t);
				}
			}
		} catch (IOException e) {
			e.printStackTrace();
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 获取单元格值
	 * @author: lottery
	 * @createTime: 2017年9月24日 下午6:46:26
	 * @history:
	 * @param cell
	 * @return Object
	 */
	@SuppressWarnings("deprecation")
	private static Object getCellValue(Cell cell) {
		Object result = null;
		if (cell != null) {
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					result = cell.getStringCellValue();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					// 对日期进行判断和解析
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						double cellValue = cell.getNumericCellValue();
						result = HSSFDateUtil.getJavaDate(cellValue);
					}
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					result = cell.getBooleanCellValue();
					break;
				case Cell.CELL_TYPE_FORMULA:
					result = cell.getCellFormula();
					break;
				case Cell.CELL_TYPE_ERROR:
					result = cell.getErrorCellValue();
					break;
				case Cell.CELL_TYPE_BLANK:
					break;
				default:
					break;
			}
		}
		return result;
	}



	// ----------------------------------- 解析excel -----------------------------------



	// ----------------------------------- 生成excel -----------------------------------

	public static <T> void saveExcelData(Class<T> clazz, List<T> list, String excelPath) {
		// 创建文件流
		File file = new File(excelPath);
		try {
			file.createNewFile();
			FileOutputStream outputStream = new FileOutputStream(file);

			// 写excel标题，第一列
			HSSFWorkbook workbook = writeExcelHead(clazz);

			// 写内容
			workbook = writeExcelData(clazz, list, workbook);

			// 写excel文件
			workbook.write(outputStream);

			// 关闭流
			workbook.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("deprecation")
	private static <T> HSSFWorkbook writeExcelHead(Class<T> clazz) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 创建sheet页
		HSSFSheet sheet = workbook.createSheet("Sheet0");

		// 设置默认列宽度
		sheet.setDefaultColumnWidth(20);

		// 单元格样式设置
		HSSFCellStyle cellStyle = createHeadCellStyle(workbook);

		List<String> filedNames = ReflectionUtils.getDeclaredFieldNames(clazz);
		HSSFRow titleRow = sheet.createRow(0);

		for (int i = 0; i < filedNames.size(); i++) {
			HSSFCell titleCell = titleRow.createCell(i);
			titleCell.setCellValue(filedNames.get(i));
			titleCell.setCellStyle(cellStyle);
		}
		return workbook;
	}

	@SuppressWarnings("deprecation")
	private static <T> HSSFWorkbook writeExcelData(Class<T> clazz, List<T> list, HSSFWorkbook workbook) {
		// 获取当前第一个sheet页
		HSSFSheet sheet = workbook.getSheetAt(0);
		List<String> fieldNames = ReflectionUtils.getDeclaredFieldNames(clazz);

		// 填充数据
		for (int i = 0; i < list.size(); i++) {
			// 从第二行开始, 第一行是主标题, 第二行是二级标题
			HSSFRow row = sheet.createRow(i + 1);
			T t = list.get(i);

			for (int j = 0; j < fieldNames.size(); j++) {
				// 反射根据字段名称获取对象中该字段的值
				Object value = ReflectionUtils.getFieldValue(t, fieldNames.get(j));
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(value.toString());

				// 设置单元格样式
				HSSFCellStyle cellStyle = createDataCellStyle(workbook);
				cell.setCellStyle(cellStyle);
			}
		}
		return workbook;
	}

	/**
	 * 设置第一行标题单元格样式
	 * @param workbook
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private static HSSFCellStyle createHeadCellStyle(HSSFWorkbook workbook) {
		if (null != headCellStyle) {
			headCellStyle = workbook.createCellStyle();
			// 单元格水平对齐方式
			headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 单元格垂直对齐方式
			headCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			// 创建字体样式
			HSSFFont font = workbook.createFont();
			// 设置字体大小
			font.setFontHeightInPoints((short) 16);

			// 为标题设置背景颜色
			headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			headCellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
			// cellStyle.setFillBackgroundColor(HSSFColor.LIGHT_GREEN.index);
			headCellStyle.setFont(font);
		}

		return headCellStyle;
	}

	private static HSSFCellStyle createDataCellStyle(HSSFWorkbook workbook) {
		if (null != dataCellStyle) {
			dataCellStyle = workbook.createCellStyle();
			// 单元格水平对齐方式
			dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			// 单元格垂直对齐方式
			dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			// 创建字体样式
			HSSFFont font = workbook.createFont();
			// 设置字体大小
			font.setFontHeightInPoints((short) 16);
			// 设置字体加粗
			font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			dataCellStyle.setFont(font);
		}

		return dataCellStyle;
	}
	// ----------------------------------- 生成excel -----------------------------------
}
